select * from Department
select * from People


--（1）假设有部门表和员工表，在添加员工的时候，该员工的部门编号如果在部门表中找不到，
--则自动添加部门信息，部门名称为"新部门"。
go
create trigger tri_newDepartment
on people for insert
as
if not exists (select * from Department where departmentId = (select departmentId from inserted))
	insert Department values ((select DepartmentId from inserted),'新部门')
go

insert People values ('006','赵云','男',5000)
--drop trigger tri_newDepartment
--delete from people where PeopleId = 9





--（2）触发器实现，删除一个部门的时候将部门下所有员工全部删除。
go
create trigger tri_deletePeopleByDepartment
on department for delete
as
delete from people where departmentId = (select departmentId from deleted)
go

delete from department where departmentId = '006'






--（4）修改一个部门编号之后，将该部门下所有员工的部门编号同步进行修改
go
create trigger tri_changeDepartmentId
on department for update
as
update people set departmentId = (select departmentId from inserted)
where departmentId = (select departmentId from deleted)
go

update department set departmentId = '007' where departmentId = '001'